package com.eascs.afterloan.dao;

import java.math.BigDecimal;
import java.util.Date;

import org.springframework.stereotype.Repository;

import com.eascs.admin.api.form.O2oAfterLoanForm;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

@Repository
public class O2oAfterDao extends BaseO2oDao {

	public PageResult<Object[]> getO2oAfterLoanByPageNew(O2oAfterLoanForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(
				" SELECT oll.loanapply_no,oll.bill_code,loan.loan_bank,oll.loan_type,CONCAT(oll.ltd_code,'-',oll.ltd_name) as ltd_name,opr.province_name,olu.user_full_name,");
		querySql.append(" CONCAT(LEFT (olu.document_no, 3),'****',RIGHT (olu.document_no, 3)) as document_no ");
		// querySql.append(" ,CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount )
		// IS NULL THEN oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql.append(" ,loan.loan_amount/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date,");
		querySql.append(
				" DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d'),oll.operator_name,oll.wf_type,oll.is_place_on_file,");
		querySql.append(
				" (SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 9944 AND d.item_code = oll.repay_mentway ) as repay_mentway");
		querySql.append(
				" ,(CASE oll.is_continue_loan WHEN 'Y' THEN '是' WHEN 'N' THEN '否' else '否' END) as is_continue_loan,");
		querySql.append(" oalfc.loan_num as loan_num,DATE_FORMAT(oalfc.the_first_date,'%Y-%m-%d') as first_time ");
		querySql.append(" ,oll.referrer_name,partner.brand_name,partner.partner_name ");
		querySql.append(
				" from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql.append(" left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(
				" LEFT JOIN (SELECT opi.partner_code,obi.brand_name,opi.partner_name from o2o_brand_info obi join o2o_partner_info opi on obi.id = opi.brand_id ");
		querySql.append(
				" union SELECT opi.pattern_code as partner_code,obi.brand_name,opi.business_license_name as partnerName from o2o_brand_info obi join o2o_dealer_partner_info opi on obi.id = opi.brand_id) as partner ");
		querySql.append(
				" on oll.partner_code = partner.partner_code left join o2o_after_loan_for_customer oalfc on oalfc.customer_id= oll.loan_id ");
		querySql.append(
				" where oll.loan_type not in ('EA','FQ') and oll.loan_flg='Y' and loan.is_del='N' and olu.sponsor_type is null and olu.spouse_id is null  ");

		querySql.append(" and (oll.creat_ro=" + UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(entity.getUsersno())) {
			querySql.append(" or oll.operator_no in (" + entity.getUsersno() + "))");
		} else {
			querySql.append(" or /**DATA_PERMISSION(oll.LTD)**/  1=1 ) ");
		}

		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND oll.BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getBankLoan())) {
			querySql.append(" AND loan.loan_bank = ?");
			sqlPageQuery.addParam(entity.getBankLoan());
		}
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" and oll.loan_type in (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND oll.OPERATOR_NAME LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {
			querySql.append(" AND olu.user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND loan.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND loan.loan_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getCreateDateStart())) {
			querySql.append(" AND  oll.create_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(entity.getCreateDateEnd())) {
			querySql.append(" AND oll.create_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getDateDueStart())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d') >= ?");
			sqlPageQuery.addParam(entity.getDateDueStart());
		}
		if (!StringUtil.isEmpty(entity.getDateDueEnd())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d') < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getDateDueEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getFirstLoanStartTime())) {
			querySql.append(" AND oalfc.the_first_date >= ?");
			sqlPageQuery.addParam(entity.getFirstLoanStartTime());
		}
		if (!StringUtil.isEmpty(entity.getFirstLoanEndTime())) {
			querySql.append(" AND oalfc.the_first_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getFirstLoanEndTime()), 1)));
		}

		/*
		 * if(!StringUtil.isEmpty(entity.getLtdName())){ querySql.append(
		 * " AND CONCAT(hu.UNITCODE,'-',hu.NAMECNSHORT) LIKE ?");
		 * sqlPageQuery.addLikeParam(entity.getLtdName()); }
		 */
		if (!StringUtil.isEmpty(entity.getLtd())) {
			querySql.append(" AND oll.ltd = ?");
			sqlPageQuery.addParam(entity.getLtd());
		}
		if (StringUtil.hasText(entity.getLtdName())) {
			querySql.append(" and CONCAT(oll.ltd_code,'-',oll.ltd_name) like ?");
			sqlPageQuery.addLikeParam(entity.getLtdName());
		}
		/*
		 * if(!StringUtil.isEmpty(entity.getProvince())){ querySql.append(
		 * " AND OL.operator_province  = ?");
		 * sqlPageQuery.addParam(entity.getProvince()); }
		 */

		// 大区查询
		/*
		 * if (!StringUtil.isEmpty(entity.getRegionalName())) {
		 * querySql.append(" AND opr.regional_name  = ?");
		 * sqlPageQuery.addParam(entity.getRegionalName()); }
		 */

		if (!StringUtil.isEmpty(entity.getHostProvince())) {
			querySql.append(" AND  opr.province_code  = ? ");
			sqlPageQuery.addParam(entity.getHostProvince());
		}
		if (!StringUtil.isEmpty(entity.getGdStatus())) {
			querySql.append(" AND oll.is_place_on_file = ?");
			sqlPageQuery.addParam(entity.getGdStatus());
		}

		if (!StringUtil.isEmpty(entity.getRepaystatus())) {
			querySql.append(" AND oll.wf_type = ?");
			sqlPageQuery.addParam(entity.getRepaystatus());
		}
		if (!StringUtil.isEmpty(entity.getIdc())) {
			querySql.append(" AND olu.document_no like ?");
			sqlPageQuery.addLikeParam(entity.getIdc());
		}
		if (!StringUtil.isEmpty(entity.getBrandName())) {
			querySql.append(" AND partner.brand_name = ?");
			sqlPageQuery.addParam(entity.getBrandName());
		}
		if (!StringUtil.isEmpty(entity.getPartnerName())) {
			querySql.append(" AND partner.partner_name like ?");
			sqlPageQuery.addLikeParam(entity.getPartnerName());
		}

		querySql.append(" ORDER BY oll.payment_time desc ");
		sqlPageQuery.setSumColumnIndexs(new int[] { 8 });
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	public PageResult<Object[]> getO2oAfterLoanExport(O2oAfterLoanForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(
				" SELECT oll.bill_code,(SELECT ppd.item_name FROM pub_dditem ppd where ppd.dd_no='10340' and loan.loan_bank=ppd.item_code) as bank_loan_name ");
		querySql.append(
				" ,(SELECT ppd.item_name FROM pub_dditem ppd where ppd.dd_no='11321' and oll.loan_type=ppd.item_code)as loan_type ");
		querySql.append(" ,CONCAT(oll.ltd_code,'-',oll.ltd_name) as ltd_name,opr.province_name,olu.user_full_name, ");
		querySql.append(" CONCAT(LEFT (olu.document_no, 3),'****',RIGHT (olu.document_no, 3)) as document_no ");
		// querySql.append(
		// " ,CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount ) IS NULL THEN
		// oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql.append(
				" ,loan.loan_amount/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date, ");
		querySql.append(" DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d'),oll.operator_name ");
		querySql.append(
				" ,(SELECT ppd.item_name FROM pub_dditem_o2o ppd where ppd.dd_no='l6ld22qj0scqv' and oll.wf_type=ppd.item_code) as wf_type ");
		querySql.append(
				" ,(SELECT ppd.item_name FROM pub_dditem_o2o ppd where ppd.dd_no='l46n7p91uggqq' and oll.is_place_on_file=ppd.item_code) as is_place_on_file ");
		querySql.append(
				" ,(SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 9944 AND d.item_code = oll.repay_mentway ) as repay_mentway ");
		querySql.append(" ,(CASE  WHEN oalfc.loan_num>1 THEN '是' else '否' END) as is_continue_loan, ");
		querySql.append(" oalfc.loan_num as loan_num,DATE_FORMAT(oalfc.the_first_date,'%Y-%m-%d') as first_time ");
		
		//上一次放款时间，经营模式（显示在第一次放款时间后面）
		querySql.append(" , (SELECT MAX(l.payment_time) FROM o2o_loanapply l   WHERE 1=1 AND l.loan_flg='Y' AND l.wf_type IN ('O','HN','DC','YQ')   ");
	    querySql.append(" AND l.payment_time <(SELECT a.payment_time FROM o2o_loanapply a WHERE a.loanapply_no = oll.loanapply_no)  AND l.loan_id = oll.loan_id  ORDER BY l.payment_time DESC) as lastDate , ");
		querySql.append(" (SELECT d.item_name 	FROM 	pub_dditem_o2o d WHERE d.DD_NO = 12001 AND d.item_code = oll.operation_model)  as operation_model ");
				  
		
		querySql.append(
				" ,oll.referrer_name,partner.brand_name,partner.partner_name,oalfb.outstanding_of_deposits,oll.bank_num ");
		querySql.append(" from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no ");
		querySql.append(" left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(
				" LEFT JOIN (SELECT opi.partner_code,obi.brand_name,opi.partner_name from o2o_brand_info obi join o2o_partner_info opi on obi.id = opi.brand_id ");
		querySql.append(
				" union SELECT opi.pattern_code as partner_code,obi.brand_name,opi.business_license_name as partnerName from o2o_brand_info obi join o2o_dealer_partner_info opi on obi.id = opi.brand_id) as partner ");
		querySql.append(
				" on oll.partner_code = partner.partner_code left join o2o_after_loan_for_billcode oalfb on oalfb.bill_code=oll.bill_code  ");
		querySql.append(" left join o2o_after_loan_for_customer oalfc on oalfc.customer_id= oll.loan_id  ");
		querySql.append(
				" where oll.loan_type not in ('EA','FQ') and oll.loan_flg='Y'  and loan.is_del='N' and olu.sponsor_type is null and olu.spouse_id is null and oll.loan_id = olu.loan_id  ");
		querySql.append(" and (oll.creat_ro=" + UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(entity.getUsersno())) {
			querySql.append(" or oll.operator_no in (" + entity.getUsersno() + "))");
		} else {
			querySql.append(" or /**DATA_PERMISSION(oll.LTD)**/  1=1 ) ");
		}
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND oll.BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getBankLoan())) {
			querySql.append(" AND loan.loan_bank = ?");
			sqlPageQuery.addParam(entity.getBankLoan());
		}
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" and oll.loan_type in (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND oll.OPERATOR_NAME LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {
			querySql.append(" AND olu.user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND loan.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND loan.loan_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getCreateDateStart())) {
			querySql.append(" AND  oll.create_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(entity.getCreateDateEnd())) {
			querySql.append(" AND oll.create_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getDateDueStart())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d') >= ?");
			sqlPageQuery.addParam(entity.getDateDueStart());
		}
		if (!StringUtil.isEmpty(entity.getDateDueEnd())) {
			querySql.append(" AND DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d') < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getDateDueEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getLtd())) {
			querySql.append(" AND oll.ltd = ?");
			sqlPageQuery.addParam(entity.getLtd());
		}
		if (StringUtil.hasText(entity.getLtdName())) {
			querySql.append(" and CONCAT(oll.ltd_code,'-',oll.ltd_name) like ?");
			sqlPageQuery.addLikeParam(entity.getLtdName());
		}
		if (!StringUtil.isEmpty(entity.getFirstLoanStartTime())) {
			querySql.append(" AND oalfc.the_first_date >= ?");
			sqlPageQuery.addParam(entity.getFirstLoanStartTime());
		}
		if (!StringUtil.isEmpty(entity.getFirstLoanEndTime())) {
			querySql.append(" AND oalfc.the_first_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getFirstLoanEndTime()), 1)));
		}

		// 大区查询
		/*
		 * if (!StringUtil.isEmpty(entity.getRegionalName())) {
		 * querySql.append(" AND opr.regional_name  = ?");
		 * sqlPageQuery.addParam(entity.getRegionalName()); }
		 */

		if (!StringUtil.isEmpty(entity.getHostProvince())) {
			querySql.append(" AND  opr.province_code  = ? ");
			sqlPageQuery.addParam(entity.getHostProvince());
		}

		if (!StringUtil.isEmpty(entity.getGdStatus())) {
			querySql.append(" AND oll.is_place_on_file = ?");
			sqlPageQuery.addParam(entity.getGdStatus());
		}

		if (!StringUtil.isEmpty(entity.getRepaystatus())) {
			querySql.append(" AND oll.wf_type = ?");
			sqlPageQuery.addParam(entity.getRepaystatus());
		}
		if (!StringUtil.isEmpty(entity.getIdc())) {
			querySql.append(" AND olu.document_no like ?");
			sqlPageQuery.addLikeParam(entity.getIdc());
		}
		if (!StringUtil.isEmpty(entity.getBrandName())) {
			querySql.append(" AND partner.brand_name like ?");
			sqlPageQuery.addParam(entity.getBrandName());
		}
		if (!StringUtil.isEmpty(entity.getPartnerName())) {
			querySql.append(" AND partner.partner_name like ?");
			sqlPageQuery.addLikeParam(entity.getPartnerName());
		}
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	/**
	 * @Description:
	 *               <p>
	 *               获取正常余额
	 *               </p>
	 * @param billCode
	 *            贷款编号
	 * @param repayDate
	 *            到期日
	 * @param repayStatus
	 *            还款状态
	 * @author CXW
	 * @date 2017年2月23日 上午10:04:30
	 * @version V1.0
	 */
	public String getpaymentsPlan(String billCode) {
		String slq = "select ol.* from o2o_loanapply ol where ol.bill_code=?";
		O2oLoanapplyEntity entity = this.findEntity(slq, O2oLoanapplyEntity.class, billCode);
		BigDecimal amount = new BigDecimal(0);
		// 1.状态为已结清的置为0
		if (entity.getWfType() != null && entity.getWfType().equals("CLO")) {
			return amount.toString();
		}
		// 2.到期日已到的置为0
		Date yestoday = new Date(new Date().getTime());
		if (entity.getSettleDate() != null && yestoday.after(entity.getSettleDate())) {
			return amount.toString();
		}
		String sql = "SELECT ifnull(sum(opp.residual_principal),0) from o2o_payments_plan opp where opp.bill_code=? and opp.payments_date>=NOW()";
		return this.findString(sql, billCode);
	}

	/**
	 * @author LQ
	 * @date 20171227
	 * @param entity
	 * @return
	 */
	public PageResult<Object[]> findArchive_QueryByPage(O2oAfterLoanForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT * FROM( ");
		// ------------------主键-------------贷款申请单号--------用户名--------信贷经理---------放款银行---------------经营平台---------------------------------------------省区----
		querySql.append(
				" SELECT oll.loanapply_no,oll.bill_code,olu.user_full_name,oll.operator_name,loan.loan_bank,CONCAT(oll.ltd_code,'-',oll.ltd_name) as ltd_name,opr.province_name");
		// ------------------贷款类型----------------------------------------------------------------------------
		querySql.append(" ,oll.loan_type ");
		// -------------------放款金额(万元)-----------------------------放款日期------------------------
		querySql.append(" ,loan.loan_amount/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') AS loan_date");
		// -------------------------------------到期日期--------------------------------------------------
		querySql.append(" ,DATE_FORMAT(ADDDATE(loan.loan_date,INTERVAL loan.loan_term  MONTH),'%Y-%m-%d') AS end_bank_date ");
		// ------------------还款状态
		querySql.append(" ,oll.wf_type ");
		querySql.append(" FROM o2o_loanapply oll ");
		querySql.append(
				" LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql.append(" LEFT JOIN o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(
				" where  /**DATA_PERMISSION(oll.ltd)**/ oll.loan_type NOT IN ('EA','FQ')  AND oll.loan_flg='Y' and loan.is_del='N' and olu.sponsor_type is null and olu.spouse_id is null  ");
		// ======================================================================
		querySql.append(" UNION ");
		querySql.append(" SELECT oll.loanapply_no,oll.bill_code,oeld.loaner_name,oll.operator_name,oeld.loan_bank ");
		querySql.append(" ,CONCAT(oll.ltd_code,'-',oll.ltd_name) AS LongltdName,opr.province_name,oeld.loan_type");
		querySql.append(" ,oeld.loan_amount/10000 AS loan_amount");
		querySql.append(" ,DATE_FORMAT(oeld.loan_date,'%Y-%m-%d') AS loan_date ");
		querySql.append(" ,DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d') AS end_bank_date ");
		querySql.append(" ,oll.wf_type ");
		querySql.append("  FROM o2o_ea_loan_data oeld  ");
		querySql.append("  LEFT JOIN o2o_loanapply oll on  oll.bill_code=oeld.bill_code_ea  ");
		querySql.append("  LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id  ");
		querySql.append("  LEFT JOIN o2o_ea_loan_settle oels on oeld.id=oels.loan_data_id  ");
		querySql.append("  where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'ZD' AND oll.wf_type NOT IN('T')");//排除接力分期的作废数据
		querySql.append(" ) T WHERE 1=1 ");
		// ===================================================================
		// 贷款单billCode
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND T.bill_code LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
		// 贷款银行bankLoan
		if (!StringUtil.isEmpty(entity.getBankLoan())) {
			querySql.append(" AND T.loan_bank = ?");
			sqlPageQuery.addParam(entity.getBankLoan());
		}
		// 信贷经理operName
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND T.operator_name LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		// 贷款人loanUserName
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {// O2oAfterLoanForm中**借款人姓名private
			// String
			// loanUserName;
			querySql.append(" AND T.user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		// loanDateStart//&loanDateEnd
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND T.loan_date >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND T.loan_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}
		// dateDueStart//dateDueEnd
		if (!StringUtil.isEmpty(entity.getDateDueStart())) {
			querySql.append(" and T.end_bank_date >= ?");
			sqlPageQuery.addParam(entity.getDateDueStart());
		}
		if (!StringUtil.isEmpty(entity.getDateDueEnd())) {
			querySql.append(" and T.end_bank_date < ?");
			sqlPageQuery.addParam(DateUtils
					.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getDateDueEnd()), 1)));
		}
		// repaystatus
		if (!StringUtil.isEmpty(entity.getRepaystatus())) {
			querySql.append(" AND T.wf_type = ?");
			sqlPageQuery.addParam(entity.getRepaystatus());
		}
		// 贷款类型
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" AND T.loan_type IN (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}

		querySql.append(" ORDER BY T.loan_date desc ");
		sqlPageQuery.setSumColumnIndexs(new int[] { 8 });
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
}
